﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace BPMS.Core.Builders
{
    public class PageQueryStatementBuilder
    {
        private string sqlStatement;
        public PageQueryStatementBuilder(string sqlStatement)
        {
            this.sqlStatement = sqlStatement;
        }
        /// <summary>
        /// 查询字段
        /// </summary>
        static Regex fieldRegex = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

        /// <summary>
        /// 排序字段
        /// </summary>
        static Regex sortRegex = new Regex(@"\bORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

        /// <summary>
        /// 去重复字段
        /// </summary>
        static Regex distinctRegex = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

        public string BuildPageQuerySql()
        {
            string sortInfo = GetSortFieldsInfo(ref sqlStatement);
            string selectInfo;
            string conditionInfo;
            string pageSql = GetQueryRecordTotalSql(sqlStatement, out selectInfo, out conditionInfo) +
                Environment.NewLine +
                string.Format(@" SELECT  *
FROM    ( SELECT     
                   {0} ,
                    ROW_NUMBER() OVER ( {2} ) AS RowNum 
                    {1}
        ) AS PageData
WHERE   RowNum BETWEEN @StartRowNum AND @EndRowNum
ORDER BY RowNum ASC", selectInfo, conditionInfo, sortInfo);
            return pageSql;
        }

        private string GetQueryRecordTotalSql(string qrySql, out string selectInfo, out string conditionInfo)
        {
            StringBuilder sbPageSql = new StringBuilder("DECLARE @RecordTotal INT ,@StartRowNum INT ,@EndRowNum INT ;");
            Group selectGroup = fieldRegex.Match(qrySql).Groups[1];
            selectInfo = selectGroup.Value;
            conditionInfo = qrySql.Substring(selectGroup.Index + selectGroup.Length);
            if (distinctRegex.IsMatch(selectInfo))
            {
                sbPageSql.AppendFormat(@" WITH RecordCount AS ({0}) SELECT @RecordTotal=COUNT(1) FROM RecordCount;", qrySql);
            }
            else
            {
                sbPageSql.AppendFormat(" SELECT @RecordTotal=COUNT(1) {0};", conditionInfo);
            }
            sbPageSql.AppendLine(@" SELECT  @RecordTotal ;
IF ( @PageIndex * @PageSize > @RecordTotal ) 
    BEGIN
        SET @PageIndex = @RecordTotal / @PageSize + 1 ;
    END
SET @StartRowNum = ( @PageIndex - 1 ) * @PageSize + 1 ;
SET @EndRowNum = @PageIndex * @PageSize ;");
            return sbPageSql.ToString();
        }

        private string GetSortFieldsInfo(ref string sql)
        {
            Match sortMatch = sortRegex.Match(sql);
            string sortInfo = "ORDER BY (SELECT NULL)";
            if (sortMatch.Success)
            {
                sortInfo = sortMatch.Value;
                sql = sortRegex.Replace(sql, "");
            }
            return sortInfo;
        }
    }

    public class PageData
    {
        public PageData( int pageIndex,int pageSize)
        {
            this.PageIndex = pageIndex;
            this.PageSize = pageSize;
        }
        private int pageIndex = 1;

        public int PageIndex
        {
            get { return pageIndex; }
            set
            {
                if (value >= 1 && value <= PageCount)
                    pageIndex = value;
            }
        }
        private int pageSize = 10;

        public int PageSize
        {
            get { return pageSize; }
            set
            {
                if (value >= 1)
                    pageSize = value;
            }
        }

        public int PageCount
        {
            get { return recordCount == 0 ? 1 : (int)Math.Ceiling((double)recordCount / pageSize); }
        }

        private int recordCount;

        public int RecordCount
        {
            get { return recordCount; }
            set { recordCount = value; }
        }
    }
}
